drop table jms_dwd.dwd_tab_loopbag_buildbag_detail --循环袋建包明细
CREATE TABLE `jms_dwd`.`dwd_tab_loopbag_buildbag_detail`
(
    site_code           varchar(50) comment '网点编号',
    proxy_area_code     varchar(50) comment '代理区编号',
    manage_code         varchar(50) comment '大区编码',
    packagecode         varchar(100) comment '包号',
    network_type        bigint comment '网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    site_name           varchar(100) comment '网点名称',
    proxy_area_name     varchar(100) comment '代理区名称',
    manage_name         varchar(100) comment '大区名称',
    chip_no             varchar(100) comment '芯片号',
    package_qty         bigint comment '包内件数',
    open_code           varchar(50) comment '拆包网点编号',
    open_site           varchar(100) comment '拆包网点名称',
    scan_time           date comment '建包时间',
    arrive_time         date comment '到件时间',
    open_time           date comment '拆包时间',
    inout_type_flag     bigint comment '建包类型:进港：包号B7-B9为进港包 出港：除B7-B9外的包号为出港包1代表进港,0代表出港',
    jt_bs_source_flag   bigint comment '建包来源:1代表JT包：B开头的极兔包,0代表BS包：4开头的纯数字包号 ',
    is_loop_pag_flag    bigint comment '循环袋标识:1代表循环袋,0代表非循环袋',
    flow_violation_flag bigint comment '流向违规标识:1违规,0代表不违规',
    day_time            date comment '查询时间'
) ENGINE = OLAP DUPLICATE KEY(site_code, proxy_area_code, manage_code,packagecode,network_type)
COMMENT "循环袋建包明细"
PARTITION BY RANGE(day_time)
(START ("2022-06-01") END ("2022-07-10") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`packagecode`) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);
